/**
 *
 */
package daotests;

import dao.JdbcRoleDao;
import dao.JdbcUserDao;
import entries.Role;
import entries.User;
import org.apache.commons.dbcp.BasicDataSource;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.dbunit.*;
import org.dbunit.database.DatabaseConfig;
import org.dbunit.database.DatabaseConnection;
import org.dbunit.database.IDatabaseConnection;
import org.dbunit.dataset.DataSetException;
import org.dbunit.dataset.IDataSet;
import org.dbunit.dataset.ITable;
import org.dbunit.dataset.xml.FlatXmlDataSetBuilder;
import org.dbunit.ext.h2.H2DataTypeFactory;
import org.dbunit.operation.DatabaseOperation;
import org.h2.tools.RunScript;

import java.io.File;
import java.io.IOException;
import java.net.MalformedURLException;
import java.sql.Connection;
import java.sql.Date;
import java.sql.SQLException;
import java.util.List;
import java.util.Properties;

/**
 * @author zharikhin
 *
 */
public class JdbcDaoTest extends DatabaseTestCase {

    private final static Log LOGGER = LogFactory.getLog(JdbcDaoTest.class);

    private String driverName;
    private String url;
    private String login;
    private String password;
    private String propertyFile = "propertiesTest.xml";

    Connection sqlConn;
    IDatabaseConnection conn;
    BasicDataSource dataSource = null;

    IDatabaseTester databaseTester;

    Properties props = new Properties();

    public JdbcDaoTest() {

        try {
            props.loadFromXML(this.getClass().getClassLoader().getResourceAsStream(propertyFile));
            driverName = props.getProperty("driverName");
            url = props.getProperty("url");
            login = props.getProperty("login");
            password = props.getProperty("password");
        } catch (Exception e) {
            LOGGER.error("Exception", e);
        };
    }

    public void setUp () {

        try {
            RunScript.execute(url, login, password, "src/tests/resources/schema.sql", "UTF8", false);
            IDataSet dataSet = getDataSet();
            importDataSet(dataSet);
        } catch (Exception e) {
            LOGGER.error("Exception", e);
        }
    }

    public void tearDown ()  {

        try {
            databaseTester.onTearDown();
        } catch (Exception e) {
            LOGGER.error("Exception", e);
        }
    }

    public void importDataSet(IDataSet dataSet) throws Exception {
        cleanlyInsert(dataSet);
    }

    @Override
    public IDataSet getDataSet()  {

        IDataSet dataset = null;

        try {
             dataset = new FlatXmlDataSetBuilder().build(new File("src/tests/resources/dataset.xml"));
        } catch (MalformedURLException e) {
            LOGGER.error("MalformedURLException", e);
        } catch (DataSetException e) {
            LOGGER.error("DataSetException", e);
        }
        return  dataset;
    }

    private void cleanlyInsert(IDataSet dataSet) throws ClassNotFoundException {

        databaseTester = new JdbcDatabaseTester(driverName, url, login, password){
            @Override
            public IDatabaseConnection getConnection() throws Exception {
                IDatabaseConnection connection = super.getConnection();

                connection.getConfig().setProperty(
                        DatabaseConfig.PROPERTY_DATATYPE_FACTORY,
                        new H2DataTypeFactory());

                return connection;
            }
        };

        databaseTester.setSetUpOperation(DatabaseOperation.CLEAN_INSERT);
        databaseTester.setDataSet(dataSet);
        try {
            databaseTester.onSetup();
        } catch (Exception e) {
            LOGGER.error("Exception", e);
        }
    }

    @Override
    protected IDatabaseConnection getConnection() {

        try {
            conn = new DatabaseConnection(createConnection());
            DatabaseConfig config = conn.getConfig();
            config.setProperty(DatabaseConfig.PROPERTY_DATATYPE_FACTORY, new H2DataTypeFactory());

        } catch (DatabaseUnitException e) {
            LOGGER.error("DatabaseUnitException", e);
        }

        return conn;
    }

    public void testUserUpdateTest () throws SQLException, ClassNotFoundException {

        JdbcUserDao userDao = new JdbcUserDao(propertyFile);

        User user = new User();
        user.setId(1);
        user.setLogin("Putin");
        user.setPassword("Gas");
        user.setEmail("asdasda");
        user.setFirstName("Vova");
        user.setLastName("Putin");
        user.setBirthDay(new Date(12313));
        Role role = new Role();
        role.setId(1);
        role.setName("Admin");
        user.setRole(role);
        userDao.update(user);

        User updated = userDao.findByLogin("Putin");
        assertEquals(1, updated.getId());
        assertEquals(1, updated.getRole().getId());
    }

    public void testFindByLoginTest() throws SQLException, ClassNotFoundException{

        JdbcUserDao userDao = new JdbcUserDao(propertyFile);
        assertNotNull("Find by login returns wrong value",userDao.findByLogin("coockoo"));
        assertEquals("Find by login returns wrong value",2, userDao.findByLogin("coockoo").getId());

    }

    public void testCreateUserTest() throws SQLException, ClassNotFoundException{

        JdbcUserDao userDao = new JdbcUserDao(propertyFile);

        User user = new User();
        user.setLogin("God");
        Role role = new Role();
        role.setId(1);
        role.setName("Admin");
        user.setRole(role);
        userDao.create(user);
        assertNotNull("user wasn't created", userDao.findByLogin("God"));
        assertEquals("user wasn't created", userDao.findByLogin("God").getRole().getId(), 1);
    }

    public void testRemoveUserTest() throws SQLException, ClassNotFoundException {

        JdbcUserDao userDao = new JdbcUserDao(propertyFile);
        User user = new User();
        user.setId(4);
        user.setLogin("God");
        Role role = new Role();
        role.setId(1);
        role.setName("Admin");
        user.setRole(role);
        userDao.create(user);

        IDatabaseConnection connection = getConnection();

        IDataSet databaseDataSet = null;
        try {
            databaseDataSet = connection.createDataSet();
            ITable actualTable = databaseDataSet.getTable("USER");
            IDataSet expectedDataSet = new FlatXmlDataSetBuilder().build(new File("resources/expected-dataset.xml"));
            ITable expectedTable = expectedDataSet.getTable("USER");
            Assertion.assertEquals(expectedTable, actualTable);

        } catch (SQLException e) {
            LOGGER.error("SQLException", e);
        } catch (MalformedURLException e) {
            LOGGER.error("MalformedURLException", e);
        } catch (DataSetException e) {
            LOGGER.error("DataSetException", e);
        } catch (DatabaseUnitException e) {
            LOGGER.error("DatabaseUnitException", e);
        }
    }

    public void testFindByEmailTest() throws SQLException, ClassNotFoundException{

        IDatabaseConnection connection = getConnection();

        try {
            IDataSet databaseDataSet = connection.createDataSet();
            ITable actualTable = databaseDataSet.getTable("USER");
            assertEquals("flame@gmail.com", actualTable.getValue(1, "EMAIL"));
        } catch (DataSetException e) {
            LOGGER.error("DataSetException", e);
        } catch (SQLException e) {
            LOGGER.error("SQLException", e);
        }

    }

    public void testFindAllUsersTest() throws SQLException, ClassNotFoundException {

        JdbcUserDao userDao = new JdbcUserDao(propertyFile);
        List<User> userList = userDao.findAll();
        assertEquals("User count must be 3", 3, userList.size());
        assertNotNull("returns wrong user list", userList.get(0));
    }

    public void testRoleUpdateTest () throws SQLException, ClassNotFoundException{

        JdbcRoleDao roleDao = new JdbcRoleDao(propertyFile);

        Role role = new Role();
        role.setId(1);
        role.setName("Admin");
        roleDao.update(role);
        Role updated = roleDao.findByName("Admin");
        assertEquals(updated.getId(), 1);
    }

    public void testRemoveRoleTest() throws SQLException, ClassNotFoundException {

        JdbcRoleDao roleDao = new JdbcRoleDao(propertyFile);
        Role role = new Role();
        role.setId(3);
        role.setName("Banned");
        roleDao.create(role);
        roleDao.remove(role);

        IDatabaseConnection connection = getConnection();

        IDataSet databaseDataSet = null;

        try {
            databaseDataSet = connection.createDataSet();
            ITable actualTable = databaseDataSet.getTable("ROLE");
            IDataSet expectedDataSet = new FlatXmlDataSetBuilder().build(new File("resources\\expected-dataset.xml"));
            ITable expectedTable = expectedDataSet.getTable("ROLE");
            Assertion.assertEquals(expectedTable, actualTable);

        } catch (SQLException e) {
            LOGGER.error("SQLException", e);
        } catch (MalformedURLException e) {
            LOGGER.error("MalformedURLException", e);
        } catch (DataSetException e) {
            LOGGER.error("DataSetException", e);
        } catch (DatabaseUnitException e) {
            LOGGER.error("DatabaseUnitException", e);
        }
    }

    public void testFindByNameTest() throws SQLException, ClassNotFoundException{

        JdbcRoleDao roleDao = new JdbcRoleDao(propertyFile);
        Role role = roleDao.findByName("User");

        assertEquals("Find role by name returns wrong value", role.getId(), Long.parseLong("2"));
    }

    // Method for setting connection pool for sql connections to test database
    private Connection createConnection(){

    if (dataSource != null){
        try {
            sqlConn = dataSource.getConnection();
        } catch (SQLException e) {
            LOGGER.error("SQLException", e);
        }
    }
    else{
        try {
            dataSource = new BasicDataSource();
            dataSource.setDriverClassName(driverName);
            dataSource.setUrl(url);
            dataSource.setUsername(login);
            dataSource.setPassword(password);
            sqlConn = dataSource.getConnection();
            return sqlConn;

        } catch (SQLException e) {
            LOGGER.error("SQLException", e);
        }
    }
    return sqlConn;
    }
}
